Excel BI - Excel Challenge 886

excel-challenges

excel-formulas

🔰 Calculate the Final Due Date for each invoice based on the following logic.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 886

Challenge Description

🔰 Calculate the Final Due Date for each invoice based on the following logic. If the cap is exceeded, then move the invoice to the next available month’s first workday. Let’s say 5 invoices are calculated to be in Mar month. Hence, Mar will have 2 invoices, Apr will have 2 invoices and May will have 1 invoice to be paid.

Solutions

library(tidyverse)
library(readxl)
library(lubridate)

path <- "Excel/800-899/886/886 Invoice Due Date Calculation.xlsx"
input <- read_excel(path, range = "A1:D40")
test <- read_excel(path, range = "E1:E40")

pay_cat = list(A = 30, B = 45, C = 60)
time_range = seq(ymd('2025-02-01'), ymd('2026-05-01'), by = '1 month')

adjust_due_date <- function(due_date) {
  case_when(
    wday(due_date) == 7 ~ due_date + days(2),
    wday(due_date) == 1 ~ due_date + days(1),
    TRUE ~ due_date
  )
}
input2 <- input %>%
  mutate(
    Due_date = case_when(
      Category == 'A' ~ Invoice_Date + days(pay_cat$A),
      Category == 'B' ~ Invoice_Date + days(pay_cat$B),
      Category == 'C' ~ Invoice_Date + days(pay_cat$C),
      TRUE ~ as.Date(NA)
    )
  ) %>%
  mutate(Due_date = adjust_due_date(Due_date))

repeat {
  input2 <- input2 %>%
    mutate(Due_month = floor_date(Due_date, unit = 'month')) %>%
    group_by(Due_month, Category) %>%
    arrange(Due_month, Category, Due_date, Invoice_Date) %>%
    mutate(row_num = row_number()) %>%
    ungroup() %>%
    mutate(
      Due_date_new = if_else(
        row_num <= 2,
        Due_date,
        adjust_due_date(ceiling_date(Due_date, unit = 'month'))
      )
    )

  if (all(input2$Due_date == input2$Due_date_new, na.rm = TRUE)) {
    break
  }
  input2$Due_date <- input2$Due_date_new
}

output <- input2 %>%
  select(-Due_date_new, -row_num, -Due_month) %>%
  arrange(Invoice_ID)

all.equal(output$Due_date, test$`Answer Expected`)
# I was defeated. My Logic doesn't cover all cases.
# Will not try with Python.

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Apply the business rule conditions explicitly.
Strengths: The transformation is organized around the correct grouping level, which keeps the business logic clear.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "800-899/886/886 Invoice Due Date Calculation.xlsx"
input_df = pd.read_excel(path, usecols="A:D", nrows=39)
test = pd.read_excel(path, usecols="E", nrows=39)

pay_days = {"A": 30, "B": 45, "C": 60}


def next_monday(dt):
    if dt.weekday() == 5:
        return dt + pd.Timedelta(days=2)
    if dt.weekday() == 6:
        return dt + pd.Timedelta(days=1)
    return dt


def first_workday_of_next_month(dt):
    return next_monday(dt.to_period("M").to_timestamp() + pd.offsets.MonthBegin(1))


def assign_due_dates(df):
    out = []
    for category, grp in df.sort_values(["Category", "Invoice_Date", "Invoice_ID"]).groupby("Category", sort=False):
        month_counts = {}
        for row in grp.itertuples(index=False):
            due = next_monday(row.Invoice_Date + pd.Timedelta(days=pay_days[category]))
            while month_counts.get(due.to_period("M"), 0) >= 2:
                due = first_workday_of_next_month(due)
            month_counts[due.to_period("M")] = month_counts.get(due.to_period("M"), 0) + 1
            out.append((row.Invoice_ID, due))
    return pd.DataFrame(out, columns=["Invoice_ID", "Due_date"])


result = (
    assign_due_dates(input_df)
    .sort_values("Invoice_ID")
    .reset_index(drop=True)
)

print(result["Due_date"].equals(test["Answer Expected"]))
# True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.